﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// JIFEN ADDED FOR CUSTOMER SERVICE ALERT MANAGEMENT
/// </summary>
public class Alert
{
	public Alert()
	{
	}

    public DataSet getAllAlertInfo(String custId)
    {
        String sql = "Select DISTINCT A.* from Alert A,ALERTSETTINGS S WHERE S.itemName = A.category and S.inUsing='Y' Order by alertId DESC";
        SqlConnection con = DBManager.getSqlConnection();
        SqlCommand cmd = new SqlCommand(sql, con);
        SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);

        DataSet myDs = new DataSet();

        myAdapter.Fill(myDs, "alert");
        return myDs;
    }

    public bool addAlertPayment(String custId, String information, String url)
    {
        return alert(custId, "PAYMENT", information, url);
    }

    public bool addAlertOccuredPayment(String custId, String information, String url)
    {
        return alert(custId, "OCCURED_PAYMENT", information, url);

    }

    public bool addAlertPersonalParticulars(String custId, String information, String url)
    {
        return alert(custId, "PERSONAL_PARTICULARS", information, url);

    }

    public bool addAlertPersonalPin(String custId, String information, String url)
    {
        return alert(custId, "PERSONAL_PIN", information, url);

    }

    public bool addAlertApplication(String custId, String information, String url)
    {
        return alert(custId, "APPLICATION", information, url);

    }

    public bool addAlertAccountDetails(String custId, String information, String url)
    {
        return alert(custId, "ACCOUNT_DETAIL", information, url);
    }

    public bool addAlertTransactions(String custId, String information, String url)
    {
        return alert(custId, "CARD_LIMIT", information, url);
    }

    private bool alert(String custId, String category, String information,String url)
    {
        string sql = "select inUsing from ALERTSETTINGS where custId=@custId and itemName=@itemName";
        SqlConnection conn = DBManager.getSqlConnection();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@custId", custId);
        cmd.Parameters.AddWithValue("@itemName", category);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        bool settingOpen = false;
        if (dr.Read())
        {
            if("Y".Equals(dr.GetString(0))){
                settingOpen = true;
            }
        }
        dr.Close();
        int row = 0;
        if (settingOpen)
        {
            sql = "INSERT INTO ALERT (category,custId,info,url) values(" +
            "@itemName,@custId,@info,@url)";
            cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@custId", custId);
            cmd.Parameters.AddWithValue("@itemName", category);
            cmd.Parameters.AddWithValue("@info", information);
            cmd.Parameters.AddWithValue("@url", url);
            row = cmd.ExecuteNonQuery();
        }
        conn.Close();
        return row > 0;
    }


    public void settingAlert(String custId, String itemName, String itemValue, Boolean inUsing)
    {
        string sql = "UPDATE ALERTSETTINGS set " +
                        "itemValue=@itemValue," +
                        "inUsing=@inUsing " +
                        "where itemName=@itemName and custId=@custId";
        
        SqlConnection conn = DBManager.getSqlConnection();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@custId", custId);
        cmd.Parameters.AddWithValue("@itemName", itemName);
        cmd.Parameters.AddWithValue("@itemValue", itemValue);
       
        if (inUsing)
        {
            cmd.Parameters.AddWithValue("@inUsing", "Y");
        }
        else
        {
            cmd.Parameters.AddWithValue("@inUsing", "N");
        }

        conn.Open();
        int row = cmd.ExecuteNonQuery();

        if (row < 1)
        {

            sql = "INSERT INTO ALERTSETTINGS (custId,itemName, itemValue,inUsing) values(" +
            "@custId, @itemName, @itemValue,@inUsing)";
               
            cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@custId", custId);
            cmd.Parameters.AddWithValue("@itemName", itemName);
            cmd.Parameters.AddWithValue("@itemValue", itemValue);
                if (inUsing)
                {
                    cmd.Parameters.AddWithValue("@inUsing", "Y");
                }
                else
                {
                    cmd.Parameters.AddWithValue("@inUsing", "N");
                }
                cmd.ExecuteNonQuery();
        }

        conn.Close();
    }

    public DataSet getAlertSettings(String custID)
    {
        String sql = "select * from ALERTSETTINGS where custID = '"+custID+"'";
        SqlConnection conn = DBManager.getSqlConnection();
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, conn);

        DataSet ds = new DataSet();
        sqlDataAdapter.Fill(ds);

        return ds;
    }


    public void confirm(int id)
    {

        string sql = "delete from ALERT " +
                       " where alertId=@itemId";

        SqlConnection conn = DBManager.getSqlConnection();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@itemId", id);
        conn.Open();
        int row = cmd.ExecuteNonQuery();
        conn.Close();
    }


    public ArrayList getAlertId(String custID, String likeMessage)
    {
        string sql = "Select alertId From Alert Where custId = '" + custID + "' and info like '%" + likeMessage + "%'";

        SqlConnection conn = DBManager.getSqlConnection();
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();

        SqlDataReader dr = cmd.ExecuteReader();
        ArrayList list = new ArrayList();

        while (dr.Read())
        {
            list.Add(dr.GetInt32(0));
        }
        dr.Close();

        conn.Close();

        return list;

    }


    public void deleteMsg(String custID, String likeMessage)
    {
       ArrayList list =  getAlertId(custID, likeMessage);
       foreach (int i in list)
       {
           confirm(i);

       }
        
    }


   


}


